﻿using Dapper;
using System.Data.SqlClient;
using System.Xml.Linq;
using yunbo.Model.SSM;

namespace yunbo.Repositorys.SSMRepositorys
{
    public class PostRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public FYPost GetFYPost(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$"SELECT * FROM [dbo].[Post]
                               ORDER BY  [post_id] 
                               OFFSET ({PageNumber}-1)*{PageSize} ROWS
                               FETCH NEXT {PageSize} ROWS ONLY;";
                List<Post> post = connection.Query<Post>(sql).ToList();
                string sql1 = "SELECT COUNT(*) FROM [dbo].[Post]";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYPost fYPost = new FYPost()
                {
                    post = post,
                    zon = zon,
                };
                return fYPost;
            }
        }
        public int AddPost(Post post)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[Post](post_name,post_code,post_status, post_sort,post_notes,post_create_by,post_create_time)\r\nvalues ('{post.post_name}','{post.post_code}','{post.post_status}','{post.post_sort}','{post.post_notes}','{post.post_create_by}','{post.post_create_time}');";
                int ps = connection.Execute(sql);
                return ps;
            }
        }
        public int UpPost(Post post)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[Post] " +
                    $"\r\nset post_name='{post.post_name}', post_code='{post.post_code}', post_status='{post.post_status}', post_sort='{post.post_sort}', post_notes='{post.post_notes}',[post_update_by]='{post.post_update_by}',[post_update_time]='{post.post_update_time}'\r\nwhere [post_id]='{post.post_id}'";
                int post1 = connection.Execute(sql);
                return post1;
            }
        }
        public int DePost(int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"delete from [dbo].[Post]\r\nwhere [post_id]={id}";
                int ps = connection.Execute(sql);
                return ps;
            }
        }
        public FYPost MHCX(int PageNumber, int PageSize, int? status, string? name)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"SELECT * FROM [dbo].[Post] WHERE post_status like '%{status}%' and post_name like '%{name}%'\r\nORDER BY  [post_id] \r\nOFFSET ({PageNumber}-1)*{PageSize} ROWS\r\nFETCH NEXT {PageSize} ROWS ONLY;";
                List<Post> posts =connection.Query<Post>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[Post] WHERE post_status like '%{status}%' and post_name like '%{name}%'";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYPost fYPost = new FYPost()
                {
                    post = posts,
                    zon = zon,
                };
                return fYPost;
            }
        }
        public int Upstatus(int id, int status)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[Post] set [post_status]='{status}'\r\nwhere [post_id]='{id}'";
                int status1 = connection.Execute(sql);
                return status1;
            }
        }
    }
}
